﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;

namespace DataAccess {
    public class CreateConnection {
        private string connectionString;        
          
        public CreateConnection()
        {
          
            connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            //con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

        }
        public CreateConnection(string con)
        {
            connectionString = con;
        }

        /// <summary>
        /// Establishes SQLs connection with the database
        /// </summary>
        /// <param name="query">The query.</param>
        /// <returns>dataset</returns>
        public DataSet CreateDataSet(ArrayList queries)
        {

            SqlConnection conn = new SqlConnection(connectionString);
            
            SqlDataAdapter adapter = new SqlDataAdapter();
            DataSet ds = new DataSet();
            int i = 0;
            try
            {
                conn.Open();
                for (i=1;i<queries.Count;i++)
                {
                    SqlCommand command = new SqlCommand(queries[i].ToString(), conn);
                    adapter.SelectCommand = command;
                    adapter.Fill(ds, "table " + i.ToString());
                    command.Dispose();
                
                }                
                adapter.Dispose();                
                conn.Dispose();
                conn.Close();
                return ds;
            }
            catch (SqlException e)
            {
                Console.Write("Err msg:" + e.Message);
                return  null;
            }
        }
        
        /// <summary>
        /// Creates the data table.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <returns></returns>
        public DataTable CreateDataTable(string query) {

            SqlConnection con = new SqlConnection(connectionString);
            try {
                if (con.State == ConnectionState.Open)
                    con.Close();
                con.Open();
                SqlDataAdapter sda = new SqlDataAdapter(query, con);
                DataTable dt = new DataTable();
                sda.Fill(dt);
                con.Dispose();
                con.Close();
                return dt;
            } catch (SqlException e) {
                Console.Write("Err msg:" + e.Message);
                return null;
            }
        }
        /// <summary>
        /// Runs the command.
        /// </summary>
        /// <param name="query">The query.</param>
        public void RunCommand(string query) {
            SqlConnection con = new SqlConnection(connectionString);
            try {
                if (con.State == ConnectionState.Open)
                    con.Close();
                con.Open();
                SqlCommand objCmd = new SqlCommand(query, con);
                objCmd.ExecuteNonQuery();
                con.Dispose();
                con.Close();
            } catch (SqlException e) {
                Console.Write("Err msg:" + e.Message);
            }
        }
        /// <summary>
        /// Runs the store procedure.
        /// </summary>
        /// <param name="storedProcedure">The stored procedure.</param>
        /// <returns></returns>
        public SqlCommand RunStoreProcedure(string storedProcedure) {
            SqlConnection con = new SqlConnection(connectionString);
            SqlCommand objCmd = new SqlCommand();
            try {
                if (con.State == ConnectionState.Open)
                    con.Close();
                con.Open();
                objCmd.CommandType = CommandType.StoredProcedure;
                objCmd.CommandText = storedProcedure;
                objCmd.Connection = con;
                con.Dispose();
                con.Close();

            } catch (SqlException e) {
                Console.Write("Err msg:" + e.Message);
            }
            return objCmd;
        }
    }
}
